using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Xml;
using System.Xml.Linq;
using System.Linq;
using System.Windows.Forms;
using System.Data.SqlClient;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data.OleDb;
using System.Data.OracleClient;
using Npgsql;

namespace GuavaData
{
    public class DBConnection : Object
    {
        private static string source;
        private static string catalog;
        private static System.Data.Common.DbConnection conn;
        private static string testCatalog;
        private static bool useTest;

        public static string MSSQL_PROVIDER = "MSSQL";
        public static string MYSQL_PROVIDER = "MySQL";
        public static string ACCESS_PROVIDER = "Access";
        public static string ORACLE_PROVIDER = "Oracle";
        public static string POSTGRES_PROVIDER = "Postgres";
        private static string provider = ACCESS_PROVIDER;

        public static string Provider
        {
            get
            {
                return provider;
            }
            set
            {
                provider = value;
            }
        }

        public static void Initialize(string s, string c, string t)
        {
            useTest = false;
            source = s;
            catalog = c;
            testCatalog = t;
            System.Data.Common.DbConnectionStringBuilder builder = null;
            if (provider == MSSQL_PROVIDER)
            {
                builder = new SqlConnectionStringBuilder();
                (builder as SqlConnectionStringBuilder).DataSource = s;
                (builder as SqlConnectionStringBuilder).InitialCatalog = c;
                (builder as SqlConnectionStringBuilder).UserID = "test";
                (builder as SqlConnectionStringBuilder).Password = "12345CoriDemo!";
                (builder as SqlConnectionStringBuilder).ConnectTimeout = 100;
                try
                {
                    conn = new System.Data.SqlClient.SqlConnection(builder.ToString());
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
            }
            else if (provider == MYSQL_PROVIDER)
            {
                builder = new MySqlConnectionStringBuilder();
                (builder as MySqlConnectionStringBuilder).Server = s;
                (builder as MySqlConnectionStringBuilder).Database = c;
                (builder as MySqlConnectionStringBuilder).UserID = "root";
                (builder as MySqlConnectionStringBuilder).Password = "123456";
                (builder as MySqlConnectionStringBuilder).ConnectionTimeout = 100;
                try
                {
                    conn = new MySqlConnection(builder.ToString());
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
            }
            else if (provider == ACCESS_PROVIDER)
            {
                builder = new OleDbConnectionStringBuilder();
                (builder as OleDbConnectionStringBuilder).DataSource = c;
                try
                {
                    conn = new OleDbConnection(builder.ToString());
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
            }
            else if (provider == ORACLE_PROVIDER)
            {
                builder = new OracleConnectionStringBuilder();
                (builder as OracleConnectionStringBuilder).DataSource = s;
                (builder as OracleConnectionStringBuilder).UserID = "sa";
                (builder as OracleConnectionStringBuilder).Password = "123";
                try
                {
                    conn = new OracleConnection(builder.ToString());
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
            }
            else if (provider == POSTGRES_PROVIDER)
            {
                String strConn = "Server=" + s + ";Port=5432;User Id=" + "postgres" + ";Password=" + "123" + ";Database=" + c + ";";
                try
                {
                    conn = new NpgsqlConnection(strConn);
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
            }

            Open();
            if (t != null)
            {
                try
                {
                    string s1 = "DROP DATABASE " + t;
                    System.Data.Common.DbCommand sc1;
                    if (conn is SqlConnection)
                        sc1 = new SqlCommand(s1, conn as SqlConnection);
                    else
                        sc1 = new MySqlCommand(s1, conn as MySqlConnection);
                    int i1 = sc1.ExecuteNonQuery();
                }
                catch
                {

                }
                string s2 = "CREATE DATABASE " + t;
                System.Data.Common.DbCommand sc2;
                if (conn is SqlConnection)
                    sc2 = new SqlCommand(s2, conn as SqlConnection);
                else
                    sc2 = new MySqlCommand(s2, conn as MySqlConnection);
                int i = sc2.ExecuteNonQuery();
            }
            Close();
        }

        public static void Initialize(string server, string database, string username, string password)
        {
            source = server;
            catalog = database;
            useTest = false;

            System.Data.Common.DbConnectionStringBuilder builder = null;
            if (provider == MSSQL_PROVIDER)
            {
                builder = new SqlConnectionStringBuilder();
                (builder as SqlConnectionStringBuilder).DataSource = source;
                (builder as SqlConnectionStringBuilder).InitialCatalog = catalog;
                (builder as SqlConnectionStringBuilder).UserID = username;
                (builder as SqlConnectionStringBuilder).Password = password;
                (builder as SqlConnectionStringBuilder).ConnectTimeout = 4;
                try
                {
                    conn = new System.Data.SqlClient.SqlConnection(builder.ToString());
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
            }
            else if (provider == MYSQL_PROVIDER)
            {
                builder = new MySqlConnectionStringBuilder();
                (builder as MySqlConnectionStringBuilder).Server = source;
                (builder as MySqlConnectionStringBuilder).Database = catalog;
                (builder as MySqlConnectionStringBuilder).UserID = username;
                (builder as MySqlConnectionStringBuilder).Password = password;
                (builder as MySqlConnectionStringBuilder).ConnectionTimeout = 4;
                try
                {
                    conn = new MySqlConnection(builder.ToString());
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
            }
            else if (provider == ACCESS_PROVIDER)
            {
                builder = new OleDbConnectionStringBuilder();
                (builder as OleDbConnectionStringBuilder).DataSource = catalog;
                (builder as OleDbConnectionStringBuilder).Provider = "Microsoft.ACE.OLEDB.12.0";
                (builder as OleDbConnectionStringBuilder).PersistSecurityInfo = false;
                try
                {
                    conn = new OleDbConnection(builder.ToString());
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
            }
            else if (provider == ORACLE_PROVIDER)
            {
                builder = new OracleConnectionStringBuilder();
                (builder as OracleConnectionStringBuilder).DataSource = catalog;
                (builder as OracleConnectionStringBuilder).UserID = username;
                (builder as OracleConnectionStringBuilder).Password = password;
                try
                {
                    conn = new OracleConnection(builder.ToString());
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
            }
            else if (provider == POSTGRES_PROVIDER)
            {
                String strConn = "Server=" + source + ";Port=5432;User Id=" + username 
                    + ";Password=" + password + ";Database=" + catalog + ";";
                try
                {
                    conn = new NpgsqlConnection(strConn);
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
            }

            Open();
            Close();
        }

        public static void useTestCatalog(bool b)
        {
            useTest = b;
        }

        public static void Open()
        {
            if (conn == null)
                return;
            if (conn.State == ConnectionState.Open) conn.Close();
            try
            {
                conn.Open();
                if (useTest)
                    conn.ChangeDatabase(testCatalog);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                throw e;
            }
        }

        public static void Close()
        {
            if (conn == null)
                return;
            conn.Close();
        }

        public static DataTable getColumns()
        {
            Open();
            string[] restrictions = new string[3];
            restrictions[0] = catalog;
            restrictions[1] = "dbo";
            DataTable dt = conn.GetSchema("Columns", restrictions);
            Close();
            return dt;
        }

        public static DataTable getForeignKeys()
        {
            Open();
            string[] restrictions = new string[4];
            restrictions[0] = catalog;
            restrictions[1] = "dbo";
            DataTable dt = conn.GetSchema("ForeignKeys", restrictions);
            Close();
            return dt;
        }

        public static DataTable getTriggers()
        {
            Open();
            DataTable dt = ExecuteQuery("select name from sys.triggers").Tables[0];
            Close();
            return dt;
        }

        public static Channel ChannelFromDB()
        {
            Open();
            Channel c = Channel.FromXDocument(XDocument.Parse(ExecuteQuery("select XMLChannel from DBVersion where version = (select max(version) from dbversion)").Tables[0].Rows[0]["XMLChannel"].ToString()));
            Close();
            return c;
        }

        public static void CreateSchema(Schema sc)
        {
            Open();

            string[] restrictions = new string[3];

            DataTable dt = conn.GetSchema("Tables");
            List<string> ls = new List<string>();
            foreach (DataRow dr in dt.Rows) ls.Add(dr["TABLE_NAME"].ToString());

            foreach (Scheme s in sc.tables)
            {
                System.Data.Common.DbCommand c;

                if (ls.Contains(s.TableName))
                {
                    restrictions[2] = s.TableName;
                    DataTable dt2 = conn.GetSchema("Columns", restrictions);
                    List<string> ls2 = new List<string>();
                    foreach (DataRow dr in dt2.Rows) ls2.Add(dr["COLUMN_NAME"].ToString());

                    // Loop through the columns and make sure they are all there
                    foreach (Column col in s.columns)
                    {
                        if (ls2.Contains(col.Name)) continue;

                        // Oops!  We have a column that doesn't exist yet.  Better make a DDL statement.
                        string alt = String.Concat("ALTER TABLE ", s.TableName, " ADD ", col.Name, " ", col.dom.DBType);
                        if (provider == MSSQL_PROVIDER)
                            c = new SqlCommand(alt, conn as SqlConnection);
                        else if (provider == MYSQL_PROVIDER)
                            c = new MySqlCommand(alt, conn as MySqlConnection);
                        else if (provider == ACCESS_PROVIDER)
                            c = new OleDbCommand(alt, conn as OleDbConnection);
                        else if (provider == ORACLE_PROVIDER)
                            c = new OracleCommand(alt, conn as OracleConnection);
                        else
                            c = new NpgsqlCommand(alt, conn as NpgsqlConnection);
                        c.ExecuteNonQuery();
                    }
                    continue;
                }
                if (provider == MSSQL_PROVIDER)
                    c = new SqlCommand(s.ToString(), conn as SqlConnection);
                else if (provider == MYSQL_PROVIDER)
                    c = new MySqlCommand(s.ToString(), conn as MySqlConnection);
                else if (provider == ACCESS_PROVIDER)
                    c = new OleDbCommand(s.ToString(), conn as OleDbConnection);
                else if (provider == ORACLE_PROVIDER)
                    c = new OracleCommand(s.ToString(), conn as OracleConnection);
                else
                    c = new NpgsqlCommand(s.ToString(), conn as NpgsqlConnection);
                c.ExecuteNonQuery();
                ls.Add(s.TableName);
            }

            Close();
        }

        public static int ExecuteNonQuery(string s)
        {
            if ((s == null) || (s == "")) return 0;
            Open();
            System.Data.Common.DbCommand sc;
            if (provider == MSSQL_PROVIDER)
                sc = new SqlCommand(s, conn as SqlConnection);
            else if (provider == MYSQL_PROVIDER)
                sc = new MySqlCommand(s, conn as MySqlConnection);
            else if (provider == ACCESS_PROVIDER)
            {
                while (s.IndexOf("'") > -1 && s.IndexOf("'") < s.IndexOf("FROM"))
                    s = s.Remove(s.IndexOf("'"), 1);
                sc = new OleDbCommand(s, conn as OleDbConnection);
            }
            else if (provider == ORACLE_PROVIDER)
                sc = new OracleCommand(s, conn as OracleConnection);
            else
                sc = new NpgsqlCommand(s, conn as NpgsqlConnection);
            sc.CommandTimeout = 3000;
            int i = sc.ExecuteNonQuery();
            Close();
            return i;
        }

        public static DataSet ExecuteQuery(string s)
        {
            Open();
            System.Data.Common.DbDataAdapter sda;
            if (provider == MSSQL_PROVIDER)
                sda = new SqlDataAdapter(s, conn as SqlConnection);
            else if (provider == MYSQL_PROVIDER)
                sda = new MySqlDataAdapter(s, conn as MySqlConnection);
            else if (provider == ACCESS_PROVIDER)
            {
                while (s.IndexOf("'") > -1 && s.IndexOf("'") < s.IndexOf("FROM"))
                    s = s.Remove(s.IndexOf("'"), 1);
                sda = new OleDbDataAdapter(s, conn as OleDbConnection);
            }
            else if (provider == ORACLE_PROVIDER)
                sda = new OracleDataAdapter(s, conn as OracleConnection);
            else
                sda = new NpgsqlDataAdapter(s, conn as NpgsqlConnection);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            Close();
            return ds;
        }
    }
}
